﻿// =================================================================== 
// 模块说明
//====================================================================
// 创建时间：2018-12-26 11:54:00
// 负责人：lvsc
// ===================================================================
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using OF.Utility;
using OF.Component.Model;
using OF.Component.IDAL;
using System.Text;
namespace OF.Component.DAL
{
	/// <summary>
	/// 数据访问层
	/// </summary>	
	internal partial class UserAuth:IUserAuth
	{		
    
         //连接字符串
         public virtual string ConnectionStringKey
         {
             get
             {
                 return ConstantUtility.CGTConnectionString;
             }
         }
           
         ///<summary>
         /// 获取最大主键ID，针对非自增列主键 
         /// 暂时无用
         ///</summary>
         public int GetMaxID()
         {
             int nReturn=0;
             string szSql=@"SELECT ISNULL(MAX(UserAuthID),0)+1
                            FROM [UserAuth]";             
             Database db = DatabaseFactory.CreateDatabase(this.ConnectionStringKey);
             DbCommand dbc = db.GetSqlStringCommand(szSql);
             try
             {
                 object o = db.ExecuteScalar(dbc);
                 if (o!=null)
                 {
                     nReturn = Convert.ToInt32(o);
                 }
             }
             catch (Exception ex)
             {
                 throw ex;
             }
             return nReturn;               
         }
                
     
         ///<summary>
         /// datareader转对象
         ///</summary>
         private UserAuthInfo GetByReader(IDataReader dr)
         {
            UserAuthInfo info = new UserAuthInfo();
            object obj;
            obj=dr["UserAuthID"];
            if (obj != null && obj != DBNull.Value)
            {                    
                info.UserAuthID = (long)obj;
            }
            obj=dr["UserID"];
            if (obj != null && obj != DBNull.Value)
            {                    
                info.UserID = (long)obj;
            }
            obj=dr["CertificatePath"];
            if (obj != null && obj != DBNull.Value)
            {                    
                info.CertificatePath = obj.ToString();
            }
            obj=dr["CertificateType"];
            if (obj != null && obj != DBNull.Value)
            {                    
                info.CertificateType = Convert.ToInt32(obj);
            }
            obj=dr["VideoPath"];
            if (obj != null && obj != DBNull.Value)
            {                    
                info.VideoPath = obj.ToString();
            }
            obj=dr["VideoScreenPath"];
            if (obj != null && obj != DBNull.Value)
            {                    
                info.VideoScreenPath = obj.ToString();
            }
            obj=dr["AuthLevel"];
            if (obj != null && obj != DBNull.Value)
            {                    
                info.AuthLevel = Convert.ToInt32(obj);
            }
            obj=dr["CertificateCreateDate"];
            if (obj != null && obj != DBNull.Value)
            {                    
                info.CertificateCreateDate = Convert.ToDateTime(obj);
            }
            obj=dr["VideoCreatedDate"];
            if (obj != null && obj != DBNull.Value)
            {                    
                info.VideoCreatedDate = Convert.ToDateTime(obj);
            }
            obj=dr["IsActive"];
            if (obj != null && obj != DBNull.Value)
            {                    
                info.IsActive = Convert.ToInt32(obj);
            }
            obj=dr["CertificateBackPath"];
            if (obj != null && obj != DBNull.Value)
            {                    
                info.CertificateBackPath = obj.ToString();
            }
            return info;
         }
         
        ///<summary>
        /// 插入接口
        ///</summary>
		public long Insert(UserAuthInfo info)
		{
            string strSql=@"INSERT INTO [UserAuth]			
			(			
			[UserID],
			[CertificatePath],
			[CertificateType],
			[VideoPath],
			[VideoScreenPath],
			[AuthLevel],
			[CertificateCreateDate],
			[VideoCreatedDate],
			[IsActive],
			[CertificateBackPath]
			)
			 VALUES
			(			
			@UserID,
			@CertificatePath,
			@CertificateType,
			@VideoPath,
			@VideoScreenPath,
			@AuthLevel,
			@CertificateCreateDate,
			@VideoCreatedDate,
			@IsActive,
			@CertificateBackPath
			)
			 SET @UserAuthID=@@IDENTITY
			";
            Database db = DatabaseFactory.CreateDatabase(this.ConnectionStringKey);
            DbCommand dbc = db.GetSqlStringCommand(strSql);
            db.AddOutParameter(dbc, "@UserAuthID",DbType.Int64, sizeof(Int64));  
            db.AddInParameter(dbc, "@UserID",DbType.Int64, info.UserID);
            db.AddInParameter(dbc, "@CertificatePath",DbType.String, info.CertificatePath);
            db.AddInParameter(dbc, "@CertificateType",DbType.Int32, info.CertificateType);
            db.AddInParameter(dbc, "@VideoPath",DbType.String, info.VideoPath);
            db.AddInParameter(dbc, "@VideoScreenPath",DbType.String, info.VideoScreenPath);
            db.AddInParameter(dbc, "@AuthLevel",DbType.Int32, info.AuthLevel);
            db.AddInParameter(dbc, "@CertificateCreateDate",DbType.DateTime, info.CertificateCreateDate.ToNullDbDateTime());
            db.AddInParameter(dbc, "@VideoCreatedDate",DbType.DateTime, info.VideoCreatedDate.ToNullDbDateTime());
            db.AddInParameter(dbc, "@IsActive",DbType.Int32, info.IsActive);
            db.AddInParameter(dbc, "@CertificateBackPath",DbType.String, info.CertificateBackPath);

             
            try
            {
                db.ExecuteNonQuery(dbc);
            }
            catch
            {	
                throw;
            }
            return (long)db.GetParameterValue(dbc,"UserAuthID");
         
		}
		
		///<summary>
        /// 修改接口
        ///</summary>
        public void Update(UserAuthInfo info)
		{
            StringBuilder sbSql=new StringBuilder("UPDATE [UserAuth] SET ");
            sbSql.AppendLine("[UserID] = @UserID,");
                
            sbSql.AppendLine("[CertificatePath] = @CertificatePath,");
                
            sbSql.AppendLine("[CertificateType] = @CertificateType,");
                
            sbSql.AppendLine("[VideoPath] = @VideoPath,");
                
            sbSql.AppendLine("[VideoScreenPath] = @VideoScreenPath,");
                
            sbSql.AppendLine("[AuthLevel] = @AuthLevel,");
                
            sbSql.AppendLine("[CertificateCreateDate] = @CertificateCreateDate,");
                
            sbSql.AppendLine("[VideoCreatedDate] = @VideoCreatedDate,");
                
            sbSql.AppendLine("[IsActive] = @IsActive,");
                
            sbSql.AppendLine("[CertificateBackPath] = @CertificateBackPath");
                
            sbSql.Append(" where  [UserAuthID] = @UserAuthID");
            Database db = DatabaseFactory.CreateDatabase(this.ConnectionStringKey);
            DbCommand dbc = db.GetSqlStringCommand(sbSql.ToString());
            db.AddInParameter(dbc, "@UserAuthID",DbType.Int64, info.UserAuthID);
            db.AddInParameter(dbc, "@UserID",DbType.Int64, info.UserID);
            db.AddInParameter(dbc, "@CertificatePath",DbType.String, info.CertificatePath);
            db.AddInParameter(dbc, "@CertificateType",DbType.Int32, info.CertificateType);
            db.AddInParameter(dbc, "@VideoPath",DbType.String, info.VideoPath);
            db.AddInParameter(dbc, "@VideoScreenPath",DbType.String, info.VideoScreenPath);
            db.AddInParameter(dbc, "@AuthLevel",DbType.Int32, info.AuthLevel);
            db.AddInParameter(dbc, "@CertificateCreateDate",DbType.DateTime, info.CertificateCreateDate.ToNullDbDateTime());
            db.AddInParameter(dbc, "@VideoCreatedDate",DbType.DateTime, info.VideoCreatedDate.ToNullDbDateTime());
            db.AddInParameter(dbc, "@IsActive",DbType.Int32, info.IsActive);
            db.AddInParameter(dbc, "@CertificateBackPath",DbType.String, info.CertificateBackPath);
			
           
            try
            {
                db.ExecuteNonQuery(dbc);
            }
            catch
            {	
                throw;
            }
		}
		
        ///<summary>
        /// 删除接口
        ///</summary>
        public void Delete(long userAuthID)
		{
			StringBuilder strSql = new StringBuilder();
            strSql.Append("DELETE FROM [UserAuth]  ");
            strSql.Append("WHERE [UserAuthID]=@UserAuthID ");
           
            Database db = DatabaseFactory.CreateDatabase(this.ConnectionStringKey);
            DbCommand dbc = db.GetSqlStringCommand(strSql.ToString());
		    db.AddInParameter(dbc, "@UserAuthID",DbType.Int64, userAuthID);
            try
            {
                db.ExecuteNonQuery(dbc);
            }
            catch
            {	
                throw;
            }
		}
        
         ///<summary>
         /// 搜索接口
         ///</summary>
         public IList<UserAuthInfo> Search(out int pageCount, int pageIndex, int pageSize, string where, string orderField, bool isDesc)
         {
            IList<UserAuthInfo> list = new List<UserAuthInfo>();
            Database db = DatabaseFactory.CreateDatabase(this.ConnectionStringKey);
            DbCommand dbc = db.GetStoredProcCommand("sp_OF_Page");
            //设置参数
            db.AddOutParameter(dbc, "@pageCount", DbType.Int32, 4);
            db.AddInParameter(dbc, "@pageIndex", DbType.Int32, pageIndex);
            db.AddInParameter(dbc, "@pageSize", DbType.Int32, pageSize);
            db.AddInParameter(dbc, "@strWhere", DbType.String, where);
            db.AddInParameter(dbc, "@fldName", DbType.String, orderField);
            db.AddInParameter(dbc, "@OrderType", DbType.Boolean, isDesc);
            db.AddInParameter(dbc, "@tblName", DbType.String, "[UserAuth]");
            db.AddInParameter(dbc, "@strGetFields", DbType.String, "*");
            using (IDataReader dr = db.ExecuteReader(dbc))
            {
                while (dr.Read())
                {
                    UserAuthInfo info = GetByReader(dr);
                    list.Add(info);
                }
            }
            pageCount = (int)db.GetParameterValue(dbc, "@PageCount");
            return list;
         }
        
      
        ///<summary>
        ///根据主键取出信息
        ///</summary>
        public UserAuthInfo GetByID(long userAuthID)
		{
            Model.UserAuthInfo  info = null;
		    StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT  * FROM [UserAuth] ");
            strSql.Append("WHERE [UserAuthID]=@UserAuthID ");
           
            Database db = DatabaseFactory.CreateDatabase(this.ConnectionStringKey);
            DbCommand dbc = db.GetSqlStringCommand(strSql.ToString());
		    db.AddInParameter(dbc, "@UserAuthID",DbType.Int64, userAuthID);
            using (IDataReader dr = db.ExecuteReader(dbc))
            {
                if (dr.Read())
                {
                    info = GetByReader(dr);
                }
            }
            return info;
		}	
	}
}